package server.phone.dao;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.utopia84.model.Bill;
import com.utopia84.model.Cashier;
import com.utopia84.model.Cashierrecord;
import com.utopia84.model.Cashierstate;

public class CashierDAO {
	DBHelper manager;
	String sql = "";
	ResultSet rs;

	public CashierDAO() throws IOException, ClassNotFoundException,
			SQLException {
		manager = DBHelper.createInstance();
	}

	public Boolean save(String changeMoney, String createTime, String userCode,
			String cashierId, String status) {
		boolean flag = false;
		sql = "insert into Cashier(changeMoney,createTime,userCode,cashierId,status) values(?,?,?,?,?)";
		Object[] params = new Object[5];
		params[0] = changeMoney;
		params[1] = createTime;
		params[2] = userCode;
		params[3] = cashierId;
		params[4] = status;

		try {
			manager.connectDB();
			flag = manager.chshierInsert(sql, params);
			manager.closeDB();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	public boolean saveInitMoney(String initMoney, String createTime,
			String userCode, String cashierId, String status) {
		boolean flag = false;
		sql = "insert into Cashier(initMoney,createTime,userCode,cashierId,status) values(?,?,?,?,?)";
		Object[] params = new Object[5];
		params[0] = initMoney;
		params[1] = createTime;
		params[2] = userCode;
		params[3] = cashierId;
		params[4] = status;

		try {
			manager.connectDB();
			flag = manager.chshierInsert(sql, params);
			manager.closeDB();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	public List<Cashier> getCasher(String name) {
		Cashier cashier = new Cashier();
		List<Cashier> cashiers = new ArrayList<Cashier>();
		sql = "select *from Cashier WHERE cashierId = '" + name
				+ "' AND closeTime IS NULL ";
		try {
			manager.connectDB();
			rs = manager.executeQuery(sql, null);
			while (rs.next()) {
				cashier = new Cashier(rs.getInt("id"),
						rs.getString("cashierId"), rs.getString("createTime"),
						rs.getString("closeTime"), rs.getFloat("initMoney"),
						rs.getFloat("different"), rs.getFloat("actual"));
				cashiers.add(cashier);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return cashiers;
	}

	public List<Cashier> getCasherADay(String name, String time) {
		Cashier cashier = new Cashier();
		String createTime = time.substring(0, 10);
		System.out.println("GetCashierADay:createTime=" + createTime);
		List<Cashier> cashiers = new ArrayList<Cashier>();
		sql = "select *from Cashier WHERE cashierId = '" + name
				+ "' AND createTime like '" + createTime + "%' ";
		System.out.println(sql);
		try {
			manager.connectDB();
			rs = manager.executeQuery(sql, null);
			while (rs.next()) {
				cashier = new Cashier(rs.getInt("id"),
						rs.getString("cashierId"), rs.getString("createTime"),
						rs.getString("closeTime"), rs.getFloat("initMoney"),
						rs.getFloat("different"), rs.getFloat("actual"));
				cashiers.add(cashier);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return cashiers;
	}

	public List<Cashier> getCasherCorrect(String name, String time) {
		Cashier cashier = new Cashier();
		System.out.println("GetCashierCorrect:createTime=" + time);
		List<Cashier> cashiers = new ArrayList<Cashier>();
		sql = "select *from Cashier WHERE cashierId = '" + name
				+ "' AND createTime = '" + time + "' ";
		System.out.println(sql);
		try {
			manager.connectDB();
			rs = manager.executeQuery(sql, null);
			while (rs.next()) {
				cashier = new Cashier(rs.getInt("id"),
						rs.getString("cashierId"), rs.getString("createTime"),
						rs.getString("closeTime"), rs.getFloat("initMoney"),
						rs.getFloat("different"), rs.getFloat("actual"));
				cashiers.add(cashier);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return cashiers;
	}

	public List<Bill> getBillByCashier(String name, String createTime) {
		Bill bill = new Bill();
		List<Bill> bills = new ArrayList<Bill>();
		sql = "select *from Bill WHERE cashierId = '" + name
				+ "' AND createTime>= '" + createTime + "'";
		try {
			manager.connectDB();
			rs = manager.executeQuery(sql, null);
			while (rs.next()) {
				bill = new Bill(rs.getString("billId"),
						rs.getString("createTime"), rs.getFloat("distant"),
						rs.getFloat("subtotal"), rs.getFloat("tax"),
						rs.getFloat("tip"), rs.getFloat("total"),
						rs.getString("waiter"), rs.getString("salerecordId"),
						rs.getFloat("rebate"), rs.getFloat("initTotal"),
						rs.getString("tipPayment"), rs.getString("payment"),
						rs.getString("cashierId"));
				bills.add(bill);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return bills;
	}

	/*
	 * 得到指定钱箱的状态
	 */
	public List<Cashierstate> getCasherState(String name) {
		Cashierstate cashier = new Cashierstate();
		List<Cashierstate> cashiers = new ArrayList<Cashierstate>();
		sql = "select * from Cashierstate WHERE cashierId = '" + name + "' ";
		try {
			manager.connectDB();
			rs = manager.executeQuery(sql, null);
			while (rs.next()) {
				cashier = new Cashierstate(rs.getString("cashierId"),
						rs.getString("state"));
				cashiers.add(cashier);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return cashiers;
	}

	public int findCIdByCashierId(String cashierId) {
		int cId = 0;
		sql = "select *from Cashier WHERE cashierId = '" + cashierId
				+ "' AND closeTime IS NULL ";
		try {
			manager.connectDB();
			rs = manager.executeQuery(sql, null);
			while (rs.next()) {
				cId = rs.getInt("id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cId;
	}

	public int findCIdByCashierIdAndTime(String cashierId, String time) {
		int cId = 0;
		sql = "select *from Cashier WHERE cashierId = '" + cashierId
				+ "' AND createTime='" + time + "' ";
		try {
			manager.connectDB();
			rs = manager.executeQuery(sql, null);
			int i = 0;
			while (rs.next()) {
				cId = rs.getInt("id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cId;
	}

	/*
	 * 得到指定钱箱放入取出记录
	 */
	public List<Cashierrecord> getCasherRecord(String name) {
		int cId = 0;
		cId = findCIdByCashierId(name);

		Cashierrecord cashier = new Cashierrecord();
		List<Cashierrecord> cashiers = new ArrayList<Cashierrecord>();
		sql = "select *from Cashierrecord WHERE cId = '" + cId
				+ "' order by time";
		try {
			manager.connectDB();
			rs = manager.executeQuery(sql, null);
			while (rs.next()) {
				cashier = new Cashierrecord(rs.getInt("id"),
						rs.getString("waiter"), rs.getString("time"),
						rs.getInt("money"), rs.getString("description"),
						rs.getInt("cId"), rs.getString("createTime"));
				cashiers.add(cashier);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cashiers;
	}

	public List<Cashierrecord> getCasherRecordByTime(String name, String time) {
		int cId = 0;
		cId = findCIdByCashierIdAndTime(name, time);

		Cashierrecord cashier = new Cashierrecord();
		List<Cashierrecord> cashiers = new ArrayList<Cashierrecord>();

		sql = "select *from Cashierrecord WHERE cId = '" + cId
				+ "' order by time";
		try {
			manager.connectDB();
			rs = manager.executeQuery(sql, null);
			while (rs.next()) {
				cashier = new Cashierrecord(rs.getInt("id"),
						rs.getString("waiter"), rs.getString("time"),
						rs.getInt("money"), rs.getString("description"),
						rs.getInt("cId"), rs.getString("createTime"));
				cashiers.add(cashier);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return cashiers;
	}

	public int saveCashier(List<Cashier> cashs) throws SQLException {
		int result = 0;
		sql = "insert into Cashier(`cashierId`, `createTime`, `initMoney`, `different`, `actual`, `closeTime`) values(?,?,?,?,?,?)";
		Object[][] params = new Object[cashs.size()][6];
		for (int i = 0; i < cashs.size(); i++) {
			params[i][0] = cashs.get(i).getCashierId();
			params[i][1] = cashs.get(i).getCreateTime();
			params[i][2] = cashs.get(i).getInitMoney();
			params[i][3] = cashs.get(i).getDifferent();
			params[i][4] = cashs.get(i).getActual();
			params[i][5] = cashs.get(i).getCloseTime();
		}
		manager.connectDB();
		result = manager.insertCashier(sql, params);
		manager.closeDB();
		return result;
	}

	public boolean updateCashier(String cashierId, String closeTime,
			float different, float actual) throws SQLException {
		boolean result = false;
		sql = "UPDATE `cashier` SET `different`=?,`actual`=?,`closeTime`=? WHERE `cashierId`=? and `closeTime` is NULL";
		Object[] params = new Object[] { different, actual, closeTime,
				cashierId };
		manager.connectDB();
		result = manager.executeUpdate(sql, params);
		manager.closeDB();
		return result;
	}

	public int saveCashierRecord(List<Cashierrecord> cashs) throws SQLException {
		int result = 0;
		sql = "insert into Cashierrecord(waiter,time,money,description,cId,createTime) values(?,?,?,?,?,?)";
		Object[][] params = new Object[cashs.size()][6];
		for (int i = 0; i < cashs.size(); i++) {
			params[i][0] = cashs.get(i).getWaiter();
			params[i][1] = cashs.get(i).getTime();
			params[i][2] = cashs.get(i).getMoney();
			params[i][3] = cashs.get(i).getDescription();
			params[i][4] = cashs.get(i).getCId();
			params[i][5] = cashs.get(i).getCreateTime();
		}
		manager.connectDB();
		result = manager.insertCashierRecord(sql, params);
		manager.closeDB();
		return result;
	}

	public int saveCashierState(List<Cashierstate> cashs) throws SQLException {
		int result = 0;
		sql = "INSERT INTO `C  ashierstate`(`cashierId`, `state`) VALUES (?,?)";
		Object[][] params = new Object[cashs.size()][2];
		for (int i = 0; i < cashs.size(); i++) {
			params[i][0] = cashs.get(i).getCashierId();
			params[i][1] = cashs.get(i).getState();
		}
		manager.connectDB();
		result = manager.insertCashierRecord(sql, params);
		manager.closeDB();
		return result;
	}

	public boolean updateCashierState(String state, String cashierId)
			throws SQLException {
		boolean result = false;
		sql = "UPDATE `Cashierstate` SET `state`=? where `cashierId`=?;";
		Object[] params = new Object[] { state, cashierId };
		manager.connectDB();
		result = manager.executeUpdate(sql, params);
		manager.closeDB();
		return result;
	}
}
